package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import pojo.Student;
import preHandle.PreHandle;

public class StudentDao {

	public static final int PAGESIZE = 20;

	public static Student getStById(int id) {
		Connection con = PreHandle.getConnection();
		PreparedStatement pstmt = null;
		ResultSet res = null;
		String sql = "select stuno,sname,sex,telephone,qq from student where stuno=?";
		try {
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(1, id);
			res = pstmt.executeQuery();
			if (res.next()) {
				return new Student(res.getString("sname"),
						res.getString("sex"), res.getString("telephone"),
						res.getString("qq"), res.getInt("stuno"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			PreHandle.closeAll(res, pstmt, con);
		}

		return null;

	}

	public static List<Student> paging(int pageNo) {
		int start = (PAGESIZE - 1) * pageNo;
		Connection con = PreHandle.getConnection();
		PreparedStatement pstmt = null;
		ResultSet res = null;
		List<Student> list = new ArrayList<Student>();
		String sql = "select * from student limit ?,?";
		try {
			int index = 1;
			pstmt = con.prepareStatement(sql);
			pstmt.setInt(index++, start);
			pstmt.setInt(index++, PAGESIZE);
			res = pstmt.executeQuery();
			while (res.next()) {
				Student s = new Student(res.getString("sname"),
						res.getString("sex"), res.getString("telephone"),
						res.getString("qq"), res.getInt("stuno"));
				list.add(s);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			PreHandle.closeAll(res, pstmt, con);
		}
		return list;
	}

	public static Integer addStudent(Student student) {
		String sql = "INSERT INTO student (sname,sex,telephone,qq,wechat,enterdate,fromcity,birthday,address,pass,gradeid)"
				+ "VALUES(?,?,?,?,?,STR_TO_DATE(?,'%Y-%m-%d'),?,STR_TO_DATE(?,'%Y-%m-%d'),?,?,?)";
		System.out.println(sql);
		try {
			int i = PreHandle.excuteUpdate(sql, student);
			return i;
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public static Integer delStudent(Integer id) {
		String sql = "DELETE FROM student where stuno=?";
		try {
			return PreHandle.excuteUpdate(sql, id);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}

	public static int uptStudent(Student student) {
		String sql = "UPDATE student SET sname=?,sex=?,telephone=?,qq=?,"
				+ "wechat=?,gradeid=?,"
				+ "enterdate=STR_TO_DATE(?,'%Y-%m-%d'),fromcity=?,"
				+ "birthday=STR_TO_DATE(?,'%Y-%m-%d'),address=?,pass=? WHERE stuno=?";

		try {
			return PreHandle.excuteUpdate(sql, student);
		} catch (IllegalArgumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;

	}
}
